package com.stx.mapper;

import com.stx.bean.Visitor;
import com.stx.utils.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*访客管理读取数据库*/
public class VisitorMapper {
    //获取访客列表
    public static List<Visitor> findVisitorsList() throws SQLException {
        List<Visitor> visitorslist = new ArrayList<>();
        Connection connection = JDBCUtil.getConnection();
        String sql = "select * from visitor";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");   //获取id
            String name = resultSet.getString("name");  //名字
            String visiteWho = resultSet.getString("visiteWho");    //被拜访人
            String phone = resultSet.getString("phone");    //联系方式
            String idCard = resultSet.getString("idCard");     //身份证号
            String visiteTime = resultSet.getString("visiteTime");//拜访时间
            Visitor visitor = new Visitor(id, name, visiteWho, phone, idCard, visiteTime);
            visitorslist.add(visitor);

        }
        return visitorslist;
    }

/*添加访客*/
    public static Boolean visitorAdd(Visitor visitor) throws SQLException {
        Connection connection = JDBCUtil.getConnection();
        String sql = "insert into visitor(id, name, visiteWho, phone, idCard,visiteTime) value (?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,visitor.getId());
        preparedStatement.setString(2,visitor.getName());
        preparedStatement.setString(3,visitor.getVisiteWho());
        preparedStatement.setString(4,visitor.getPhone());
        preparedStatement.setString(5,visitor.getIdCard());
        preparedStatement.setString(6, visitor.getVisiteTime());
        int i = preparedStatement.executeUpdate();
        return true;
    }
    //根据id查找访客
    public static Visitor findVisitorById(String id) throws SQLException {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select * from visitor where id = ? ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String name = resultSet.getString("name");  //名字
            String visiteWho = resultSet.getString("visiteWho");    //被拜访人
            String phone = resultSet.getString("phone");    //联系方式
            String idCard = resultSet.getString("idCard");     //身份证号
            String visiteTime = resultSet.getString("visiteTime");//拜访时间
            Visitor visitor = new Visitor(id, name, visiteWho, phone, idCard, visiteTime);
            return visitor;
        }
        return null;
    }

    //更新访客信息
    public static Boolean visitorUpdate(Visitor visitor) throws SQLException{
        Connection connection = JDBCUtil.getConnection();

        String sql = "update visitor set name=?,visiteWho=?,phone=?,idCard=?,visiteTime=? where id =?" ;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,visitor.getName());
        preparedStatement.setString(2,visitor.getVisiteWho());
        preparedStatement.setString(3,visitor.getPhone());
        preparedStatement.setString(4,visitor.getIdCard());
        preparedStatement.setString(5, visitor.getVisiteTime());
        preparedStatement.setString(6, visitor.getId());
        int i = preparedStatement.executeUpdate();
        return true;
    }
    //根据id删除访客
    public static Boolean delVisitorById(String id) throws Exception {
        Connection connection = JDBCUtil.getConnection();
        String sql = "delete from visitor where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,id);
        int i = preparedStatement.executeUpdate();
        return true;
    }
}
